SQL中with as用法 | 您所在的位置:网站首页 › now that的用法总结及典型句子 › SQL中with as用法 |
WITH AS短语,也叫做子查询部分,定义一个SQL片断后,该SQL片断可以被整个SQL语句所用到。有的时候,with as是为了提高SQL语句的可读性,减少嵌套冗余。 with A as ( select * from user ) select * from A, customer where customer.userid = user.id**先执行select * from user把结果放到一个临时表A中,作为全局使用。 with as的用法可以通俗点讲是,讲需要频繁执行的slq片段加个别名放到全局中,后面直接调用就可以,这样减少调用次数,优化执行效率。 语法: 针对一个别名 with tmp as (select * from tb_name) 针对多个别名 with tmp as (select * from tb_name), tmp2 as (select * from tb_name2), tmp3 as (select * from tb_name3), … 例子: –相当于建了个e临时表 with e as (select * from scott.emp e where e.empno=7499) select * from e; –相当于建了e、d临时表 with e as (select * from scott.emp), d as (select * from scott.dept)select * from e, d where e.deptno = d.deptno; 与UNION ALL结合使用 with sql1 as (select to_char(a) s_name from test_tempa), sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))select * from sql1 union all select * from sql2 union all select ‘no records’ from dual where not exists (select s_name from sql1 where rownum=1) and not exists (select s_name from sql2 where rownum=1);WITH语句的优点: 1、可以轻松构建一个临时表,通过对这个表数据进行再处理。但是他比临时表更强大,临时表在会话结束才会自动被P清除,但with as临时表查询完成后就被清除了 2、复杂的查询会产生很大的sql,with as语法可以把一些公共查询提出来,也可以作为一个中间结果,可以使整个sql语句显得有条理些,提高可读性 |
CopyRight 2018-2019 实验室设备网 版权所有 |